let express = require('express');
let router = express.Router();
const { updateSql, insertSql } = require('../../utils');
let connection = require('../dataBase');
const Result = require('../result');

// 获取账号对文章的行为
router.get('/get', function (req, res) {
  const sql = `
  SELECT
    act.*,
    att.otherAccountId oaid,
    att.id attId
  FROM
    ( SELECT action.*, article.accountId otherAccountId FROM action LEFT JOIN article ON article.id = action.articleId ) act
    LEFT JOIN ( SELECT id,accountId, otherAccountId FROM attention WHERE accountId = ${req.query.accountId} ) att ON act.accountId = att.accountId 
    AND act.otherAccountId = att.otherAccountId 
  WHERE
    act.accountId = ${req.query.accountId}
    AND act.articleId = ${req.query.articleId}
  `;
  connection.query(sql, (err, data) => {
    if (err) {
      return new Result(res, 1200).fail();
    }
    return new Result(res, 200, data, '添加成功').success();
  });
});

// 修改或添加
router.post('/update', function (req, res) {
  const sql = req.body.id
    ? updateSql('action', req.body.id, req.body)
    : insertSql('action', req.body);
  connection.query(sql, (err, data) => {
    if (err) {
      return new Result(res, 1200).fail();
    }
    console.log(sql);
    return new Result(res, 200, data, '添加成功').success();
  });
});

// 获取账号的所有收藏
router.get('/getAllCollection', function (req, res) {
  let sql = `SELECT art2.*,act.id actionId FROM action act LEFT JOIN (SELECT DISTINCT
      art.*,
      li.likeNum,
      li.starNum,
      li.lookNum,
      com.commentNum 
    FROM
      article art
      LEFT JOIN (
      SELECT
        articleId,
        count( action.islike ) likeNum,
        count( action.star ) starNum,
        count( action.look ) lookNum 
      FROM
        action 
      GROUP BY
        articleId 
      ) li ON art.id = li.articleId
      LEFT JOIN ( SELECT articleId, count( * ) commentNum FROM ${'`comment`'} WHERE ${'`level`'} = 0 GROUP BY articleId ) com ON art.id = com.articleId
    GROUP BY
      art.id 
    ORDER BY
      time DESC 
    ) art2 ON act.articleId = art2.id 
  WHERE
    act.star = 1 
    AND act.accountId = ${req.query.accountId}`;
  sql = sql.replace(/\s+|[\r\n]/g, ' ');
  console.log(sql);
  connection.query(sql, (err, data) => {
    if (err) {
      return new Result(res, 1200).fail();
    }
    return new Result(res, 200, data, '添加成功').success();
  });
});

module.exports = router;
